﻿using DBUtil.MetaData;
using DBUtil.Util;
using DotNetCommon.Data;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Index = DBUtil.MetaData.Index;

namespace DBUtil
{
    /// <summary>
    /// 数据库管理对象
    /// </summary>
    public abstract class DBManage
    {
        /// <summary>
        /// 数据库访问对象
        /// </summary>
        protected readonly DBAccess db;

        /// <summary>
        /// 根据DBAccess创建DBManage
        /// </summary>
        /// <param name="db"></param>
        public DBManage(DBAccess db)
        {
            this.db = db;
        }

        #region 获取元数据信息
        /// <summary>
        /// 返回所有的架构/模式
        /// </summary>
        /// <returns></returns>
        public virtual List<Schema> ShowSchemas()
        {
            var sql = $"select * from INFORMATION_SCHEMA.SCHEMATA";
            var dt = db.SelectDataTable(sql);
            var res = new List<Schema>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                res.Add(new Schema()
                {
                    Name = dt.Rows[i]["SCHEMA_NAME"].ToString(),
                    DataBaseName = dt.Rows[i]["CATALOG_NAME"].ToString(),
                    Owner = dt.Rows[i]["SCHEMA_OWNER"].ToString()
                });
            }
            return res;
        }

        #region 表的元数据
        /// <summary>
        /// 返回所有的表
        /// </summary>
        /// <param name="schemaName">指定schema名称</param>
        /// <param name="isDetail">是否给表填充详细信息,默认不填充</param>
        public abstract List<Table> ShowTables(string schemaName, bool isDetail = false);

        /// <summary>
        /// 返回指定表的详细元数据
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="schemaName">模式名称</param>
        public abstract Table ShowTableDetail(string tableName, string schemaName = null);

        /// <summary>
        /// 返回指定表的所有列
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="schemaName">模式名称</param>
        /// <returns></returns>
        public abstract List<Column> ShowTableColumns(string tableName, string schemaName = null);

        /// <summary>
        /// 返回指定表的所有索引
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="schemaName">模式名称</param>
        /// <returns></returns>
        public abstract List<Index> ShowTableIndexes(string tableName, string schemaName = null);

        /// <summary>
        /// 返回指定表的所有触发器
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="schemaName">模式名称</param>
        /// <returns></returns>
        public abstract List<Trigger> ShowTableTriggers(string tableName, string schemaName = null);

        /// <summary>
        /// 返回表的所有约束信息
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="schemaName">模式名称</param>
        /// <returns></returns>
        public abstract List<MetaData.Constraint> ShowTableConstraints(string tableName, string schemaName = null);
        #endregion

        #region 视图原数据
        /// <summary>
        /// 返回所有的视图(仅用户自定义的)
        /// </summary>
        /// <param name="schemaName">指定schema名称</param>
        public abstract List<View> ShowViews(string schemaName = null);

        /// <summary>
        /// 返回指定视图的详细元数据
        /// </summary>
        /// <param name="viewName">视图名称</param>
        /// <param name="schemaName">模式名称</param>
        public abstract View ShowViewDetail(string viewName, string schemaName = null);
        #endregion

        /// <summary>
        /// 返回所有的存储过程(仅用户自定义的)
        /// </summary>
        /// <param name="schemaName">指定schema名称</param>
        /// <returns></returns>
        public abstract List<Procedure> ShowProcedures(string schemaName = null);

        /// <summary>
        /// 返回所有的函数(仅用户自定义的)
        /// </summary>
        /// <param name="schemaName">指定schema名称</param>
        /// <returns></returns>
        public abstract List<Function> ShowFunctions(string schemaName = null);

        /// <summary>
        /// 返回所有的序列
        /// </summary>
        /// <param name="schemaName">指定schema名称</param>
        /// <returns></returns>
        public abstract List<Sequence> ShowSequences(string schemaName = null);
        #endregion

        #region 修改数据库元数据
        /// <summary>
        /// 根据表结构创建表
        /// </summary>
        /// <param name="table">表结构</param>
        public abstract Result CreateTable(Table table);

        /// <summary>
        /// 给指定表添加一列
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="column">列</param>
        /// <param name="schemaName">指定schema名称</param>
        /// <returns></returns>
        public abstract Result AddColumn(string tableName, Column column, string schemaName = null);

        /// <summary>
        /// 删除指定表的指定列
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">要删除的列名</param>
        /// <param name="schemaName">指定schema名称</param>
        /// <returns></returns>
        public abstract Result DropColumn(string tableName, string columnName, string schemaName = null);

        /// <summary>
        /// 删除指定表
        /// </summary>
        /// <param name="tableName">要删除的表</param>
        /// <param name="schemaName">指定schema名称</param>
        /// <returns></returns>
        public virtual Result DropTable(string tableName, string schemaName = null)
        {
            return Result.Wrap(() =>
            {
                string sql = "drop table " + tableName + "";
                db.ExecuteSql(sql);
            });
        }

        /// <summary>
        /// 删除指定表
        /// </summary>
        /// <param name="tableName">要删除的表</param>
        /// <param name="schemaName">指定schema名称</param>
        /// <returns></returns>
        public abstract Result DropTableIfExist(string tableName, string schemaName = null);

        /// <summary>
        /// 重命名表名
        /// </summary>
        /// <param name="oldName">旧表名</param>
        /// <param name="newName">新表名</param>
        /// <param name="schemaName">指定shema名称</param>
        /// <remarks>注意: 不可能在修改表名时改变所属shcema</remarks>
        /// <returns></returns>
        public abstract Result RenameTable(string oldName, string newName, string schemaName = null);

        /// <summary>
        /// 重命名列名
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="oldName">旧列名</param>
        /// <param name="newName">新列名</param>
        /// <param name="schemaName">指定schema名称</param>
        /// <returns></returns>
        public abstract Result RenameColumn(string tableName, string oldName, string newName, string schemaName = null);

        /// <summary>
        /// 修改表说明
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="desc">说明信息</param>
        /// <param name="schemaName">指定schema名称</param>
        public abstract Result UpdateTableDescription(string tableName, string desc, string schemaName = null);

        /// <summary>
        /// 修改视图说明
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="desc">说明信息</param>
        /// <param name="schemaName">指定schema名称</param>
        public abstract Result UpdateViewDescription(string tableName, string desc, string schemaName = null);

        /// <summary>
        /// 修改列说明
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colName">列名</param>
        /// <param name="desc">说明信息</param>
        /// <param name="schemaName">指定schema名称</param>
        public abstract Result UpdateColumnDescription(string tableName, string colName, string desc, string schemaName = null);

        /// <summary>
        /// 删除触发器
        /// </summary>
        /// <param name="triggerName">触发器名称</param>
        /// <param name="schemaName">指定schema名称</param>
        /// <remarks>
        /// 注意: 表级触发器必须和表相同schema，sqlserver的报错提示如下:<para></para>
        /// 因为触发器 'testschema.tri_test_after_insert' 的架构不同于目标表或视图的架构，所以无法 创建 该触发器。
        /// </remarks>
        /// <returns></returns>
        public virtual Result DropTrigger(string triggerName, string schemaName = null)
        {
            return Result.Wrap(() =>
            {
                db.ExecuteSql($"drop trigger {triggerName}");
            });
        }

        /// <summary>
        /// 删除触发器
        /// </summary>
        /// <param name="triggerName">触发器名称</param>
        /// <param name="schemaName">指定schema名称</param>
        /// <returns></returns>
        public abstract Result DropTriggerIfExist(string triggerName, string schemaName = null);

        /// <summary>
        /// 删除存储过程
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="schemaName">指定schema名称</param>
        /// <returns></returns>
        public virtual Result DropProcedure(string procName, string schemaName = null)
        {
            return Result.Wrap(() =>
            {
                db.ExecuteSql($"drop procedure {procName}");
            });
        }

        /// <summary>
        /// 删除存储过程
        /// </summary>
        /// <param name="triggerName">存储过程名称</param>
        /// <param name="schemaName">指定schema名称</param>
        /// <returns></returns>
        public abstract Result DropProcedureIfExist(string triggerName, string schemaName = null);
        #endregion

        #region 生成insert语句
        /// <summary>
        /// 根据表名称和过滤条件生成表数据的insert语句
        /// </summary>
        /// <param name="schemaName">模式名称</param>
        /// <param name="tableName">表名称</param>
        /// <param name="limitCount">限制导出的数量(当数据量很大时使用,0:表示全部导出)</param>
        /// <param name="filter">过滤条件(以and开头)</param>
        /// <param name="paras">参数</param>
        /// <returns></returns>
        public abstract (long count, string sql, long total) GenerateInsertSql(string schemaName, string tableName, int limitCount = 0, string filter = null, params IDataParameter[] paras);

        /// <summary>
        /// 将表数据全部导出到文件
        /// </summary>
        /// <param name="schemaName">模式名称</param>
        /// <param name="tableName">表名</param>
        /// <param name="fileAbsPath">文件的绝对路径</param>
        /// <param name="limitCount">限制导出的数量(当数据量很大时使用,0:表示全部导出)</param>
        /// <param name="filter">过滤条件(以and开头)</param>
        /// <param name="paras">参数</param>
        /// <returns>导出的记录数</returns>
        public abstract long GenerateInsertSqlFile(string schemaName, string tableName, string fileAbsPath, int limitCount = 0, string filter = null, params IDataParameter[] paras);
        #endregion

        #region truncate指定表
        /// <summary>
        /// truncate指定表
        /// </summary>
        /// <param name="tableName">模式名</param>
        /// <param name="shemaName">表名</param>
        public abstract void TruncateTable(string tableName, string shemaName = null);
        #endregion

        #region 获取记录数
        /// <summary>
        /// 获取表的记录数
        /// </summary>
        /// <param name="schemaName">模式名</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public abstract long GetCount(string tableName, string schemaName = null);
        #endregion

        #region 生成建表脚本
        /// <summary>
        /// 生成建表脚本
        /// </summary>
        /// <param name="shemaName">模式名</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public abstract string GenerateCreateTableSql(string tableName, string shemaName = null);

        /// <summary>
        /// 生成建表脚本文件
        /// </summary>
        /// <param name="tables">表名集合</param>
        /// <param name="fileAbsPath">输出文件的绝对路径</param>
        /// <param name="includeInsertSql">是否包含insert脚本</param>
        /// <returns></returns>
        public abstract void GenerateCreateTableSqlFile(List<Table> tables, string fileAbsPath, bool includeInsertSql = false);
        #endregion

        #region 根据表生成增删改查代码
        /// <summary>
        /// 根据表生成增删改查代码
        /// </summary>
        /// <param name="shemaName">模式名称</param>
        /// <param name="tableName">表名称</param>
        /// <returns></returns>
        public abstract string GenerateCode(string tableName, string shemaName = null);
        #endregion
    }
}
